
CREATE OR REPLACE FUNCTION f_cx_userAI() RETURNS TRIGGER AS $f_cx_userAI$
  DECLARE
    v_defpsw      VARCHAR(64);
    v_count       NUMERIC(9);
  BEGIN
    SELECT count(1) INTO v_count FROM cx_syscfg WHERE no='DefPsw';
    IF v_count>0 THEN
      SELECT value INTO v_defpsw FROM cx_syscfg WHERE no='DefPsw';
    ELSE
      v_defpsw:='W9YDMCv0sIA7stOkxEfqCA==';
    END IF;

    INSERT INTO cx_sysuser(id,no,name,password) VALUES(NEW.id,NEW.no,NEW.name,v_defpsw);
    INSERT INTO cx_userdep(userid,username,organid,organname) VALUES(NEW.id,NEW.name,NEW.glid,(select namec from cx_organ where id=NEW.glid));
    RETURN NEW;
  END;
$f_cx_userAI$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_userAI AFTER INSERT ON cx_user FOR EACH ROW EXECUTE PROCEDURE f_cx_userAI();
/**/

CREATE OR REPLACE FUNCTION f_cx_userAU() RETURNS TRIGGER AS $f_cx_userAU$
  DECLARE
    v_defpsw      VARCHAR(64);
    v_count       NUMERIC(9);
  BEGIN
    IF OLD.state=0 AND NEW.state=1 THEN
      UPDATE cx_sysuser SET password='state=1' WHERE id=OLD.id;
    ELSIF OLD.state=1 AND NEW.state=0 THEN
      SELECT count(1) INTO v_count FROM cx_syscfg WHERE no='DefPsw';
      if v_count>0 then
        SELECT value INTO v_defpsw FROM cx_syscfg WHERE no='DefPsw';
      else
        v_defpsw:='W9YDMCv0sIA7stOkxEfqCA==';   -- zbcx@15!
      END IF;
      UPDATE cx_sysuser SET password=v_defpsw WHERE id=OLD.id;
    END IF;

    IF NEW.name!=OLD.name OR NEW.no!=OLD.no THEN
      UPDATE cx_sysuser SET no=NEW.no,name=NEW.name WHERE id=OLD.id;
      UPDATE cx_userdep SET username=NEW.name WHERE userid=OLD.id;
    END IF;
    RETURN NEW;
  END;
$f_cx_userAU$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_userAU AFTER UPDATE ON cx_user FOR EACH ROW EXECUTE PROCEDURE f_cx_userAU();
/**/


CREATE OR REPLACE FUNCTION f_cx_userBD() RETURNS TRIGGER AS $f_cx_userBD$
  BEGIN
    DELETE FROM cx_userdep   WHERE userid=OLD.id;
    DELETE FROM cx_userrole  WHERE userid=OLD.id;
    DELETE FROM cx_usergroup WHERE userid=OLD.id;
    DELETE FROM cx_depadm    WHERE userid=OLD.id;
    DELETE FROM cx_userhabit WHERE userid=OLD.id;
    RETURN OLD;
  END;
$f_cx_userBD$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_userBD BEFORE DELETE ON cx_user FOR EACH ROW EXECUTE PROCEDURE f_cx_userBD();
/**/


CREATE OR REPLACE FUNCTION f_cx_userAD() RETURNS TRIGGER AS $f_cx_userAD$
  BEGIN
    DELETE FROM cx_sysuser WHERE id=OLD.id;
    RETURN OLD;
  END;
$f_cx_userAD$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_userAD AFTER DELETE ON cx_user FOR EACH ROW EXECUTE PROCEDURE f_cx_userAD();
/**/



CREATE OR REPLACE FUNCTION f_cx_sysroleBD() RETURNS TRIGGER AS $f_cx_sysroleBD$
  BEGIN
    DELETE FROM cx_userrole   WHERE roleid=old.id;
    DELETE FROM cx_sysroleobj WHERE roleid=old.id;
    RETURN OLD;
  END;
$f_cx_sysroleBD$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_sysroleBD BEFORE DELETE ON cx_sysrole FOR EACH ROW EXECUTE PROCEDURE f_cx_sysroleBD();
/**/


CREATE OR REPLACE FUNCTION f_cx_sysroleAU() RETURNS TRIGGER AS $f_cx_sysroleAU$
  BEGIN
    IF NEW.vry!=OLD.vry THEN
      UPDATE cx_userrole SET vry=NEW.vry WHERE roleid=NEW.id;
    END IF;
    RETURN NEW;
  END;
$f_cx_sysroleAU$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_sysroleAU AFTER UPDATE ON cx_sysrole FOR EACH ROW EXECUTE PROCEDURE f_cx_sysroleAU();
/**/


CREATE OR REPLACE FUNCTION f_cx_organBU() RETURNS TRIGGER AS $f_cx_organBU$
DECLARE
    _code   VARCHAR(32) DEFAULT NULL;
    changed smallint DEFAULT 0;
BEGIN
    IF OLD.code != NEW.code THEN
        SELECT new.code INTO _code;
        SET changed = 1;
    END IF;

    IF changed = 1 THEN
        UPDATE cx_userdep SET organname = _code WHERE organid=OLD.id;
    END IF;
    RETURN NEW;
END;
$f_cx_organBU$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_organBU AFTER UPDATE ON cx_organ FOR EACH ROW EXECUTE PROCEDURE f_cx_organBU();
/**/

CREATE OR REPLACE FUNCTION f_cx_organBD() RETURNS TRIGGER AS $f_cx_organBD$
BEGIN
    DELETE FROM cx_userdep WHERE organid=OLD.id;
    DELETE FROM cx_depadm  WHERE id0=OLD.id;
END;
$f_cx_organBD$ LANGUAGE PLPGSQL;
CREATE TRIGGER cx_organBD BEFORE DELETE ON cx_organ FOR EACH ROW EXECUTE PROCEDURE f_cx_organBD();
/**/